#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

dws_finishcourse_num_nd="
set hive.strict.checks.type.safety=false;
insert overwrite table dws_finishcourse_num_nd partition (dt='${do_date}')
select
course_id,
sum(if(dt>=date_sub('${do_date}',6),course_complete_user_num,0)),
sum(course_complete_user_num),
sum(if(dt>=date_sub('${do_date}',6),chapter_complete_num,0)),
sum(chapter_complete_num)
from dws_finishcourse_num_1d
where dt >= date_sub('${do_date}',29)
group by course_id, '${do_date}';
"

dws_test_exam_1d="
insert overwrite table dws_test_exam_1d partition(dt='${do_date}')
select paper_id,
       paper_title,
       course_id,
       course_name,
       count(1) num,
       count(distinct user_id) user_num,
       sum(score) score_sum,
       sum(duration_sec) duration_sec_sum
from dwd_test_exam_inc
where dt='${do_date}'
group by paper_id, paper_title,course_id,course_name,dt;
"

dws_test_course_scorestage_1d="
insert overwrite table dws_test_course_scorestage_1d partition(dt='${do_date}')
select course_id,
       course_name,
       score_stage,
       count(distinct user_id) user_num,
       count(*) num
from dwd_test_exam_inc
where dt = '${do_date}'
group by course_id, course_name,score_stage,dt;
"


dws_test_question_1d="
insert overwrite table dws_test_question_1d partition(dt='${do_date}')
 select question_id,
        question_txt,
        sum(if(is_correct = '1',1,0)) correct_num,
        count(1) total_num
 from dwd_test_exam_inc
 where dt = '${do_date}'
 group by question_id, question_txt,dt;
"
dws_order_category_lidan_1d="
insert overwrite table dws_order_category_lidan_1d partition(dt='${do_date}')
select a.id,
       b.category_name,
       a.user_id,
       a.final_amount
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt='${do_date}' and b.dt='${do_date}';
"
dws_order_subject_lidan_1d="
insert overwrite table dws_order_subject_lidan_1d partition(dt='${do_date}')
select a.id,
       b.subject_name,
       a.user_id,
       a.final_amount
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt='${do_date}' and b.dt='${do_date}';
"
dws_order_course_lidan_1d="
insert overwrite table dws_order_course_lidan_1d partition(dt='${do_date}')
select a.id,
       b.course_name,
       a.user_id,
       a.final_amount
from dwd_order_detail_lidan_inc a
     left join dim_course_info_lidan_full b
               on a.course_id=b.id
where a.dt='${do_date}' and b.dt='${do_date}';
"
dws_review_info_course_lidan_1d="
insert overwrite table dws_review_info_course_lidan_1d partition(dt='${do_date}')
select b.course_name,
       sum(if(a.dt='${do_date}',a.review_stars,0)) total_review_score_1d,
       count(distinct if(a.dt='${do_date}',a.user_id,null)) review_user_num_1d,
       count(if(a.dt='${do_date}',a.id,null)) count_review_1d,
       count(if(a.dt='${do_date}' and review_stars='5',a.id,null)) count_good_review_1d,
       sum(if(a.dt>=date_add('${do_date}',-6),a.review_stars,0)) total_review_score_7d,
       count(distinct if(a.dt>=date_add('${do_date}',-6),a.user_id,null)) review_user_num_7d,
       count(if(a.dt>=date_add('${do_date}',-6),a.id,null)) count_review_7d,
       count(if(a.dt>=date_add('${do_date}',-6) and review_stars='5',a.id,null)) count_good_review_7d,
       sum(if(a.dt>=date_add('${do_date}',-29),a.review_stars,0)) total_review_score_30d,
       count(distinct if(a.dt>=date_add('${do_date}',-29),a.user_id,null)) review_user_num_30d,
       count(if(a.dt>=date_add('${do_date}',-29),a.id,null)) count_review_30d,
       count(if(a.dt>=date_add('${do_date}',-29) and review_stars='5',a.id,null)) count_good_review_30d
from dwd_review_info_lidan_inc a
     left join dim_course_info_lidan_full b ON a.course_id=b.id
where a.dt>=date_add('${do_date}',-29) AND a.dt<='${do_date}' and b.dt='${do_date}'
group by b.course_name;
"
dws_user_retention_category_lidan_1d="
with category_order as(
	select t1.id,
	       t1.course_id,
	       t1.course_name,
	       t2.subject_name,
	       t2.category_name,
	       t1.order_id,
	       t1.user_id,
	       t1.origin_amount,
	       t1.coupon_reduce,
	       t1.final_amount,
	       t1.create_time,
	       t1.update_time,
	       t1.dt
	from dwd_order_detail_lidan_inc t1
	     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
	WHERE t1.dt='${do_date}' and t2.dt='${do_date}'
                     )


insert overwrite table dws_user_retention_category_lidan_1d partition(dt='${do_date}')



select a.category_name,
       a.user_id,
       nvl(b.create_time,a.create_time) create_time,
       nvl(b.final_amount,c.final_amount) final_amount,
       nvl(b.min_order_create_time,c.min_order_create_time) min_order_create_time,
       nvl(b.diff_day,datediff(nvl(b.min_order_create_time,c.min_order_create_time),nvl(b.create_time,a.create_time))+1),
       a.dt
from
	(
		select t2.category_name,
		       t1.user_id,
		       min(t1.create_time) create_time,
		       t1.dt
		from dwd_user_chapter_process_lidan_inc t1
		     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
		where t1.dt <= '${do_date}'
		  and t1.dt >= date_add('${do_date}', -6) and t2.dt='${do_date}'
		group by t2.category_name,t1.user_id,t1.dt
	)a
left join
	(
		select category_name,
		       user_id,
		       create_time,
		       final_amount,
		       min_order_create_time,
		       diff_day,
		       try_dt
	    from dws_user_retention_category_lidan_1d
	    where dt=date_add('${do_date}',-1)
	)b
on a.category_name=b.category_name and a.user_id=b.user_id
left join(
		select category_name,
		       user_id,
		       final_amount,
		       min_order_create_time
		from(
			    select category_name,
			           user_id,
			           final_amount,
			           create_time,
			           min(create_time) over(partition by category_name,user_id) min_order_create_time
			    from category_order
			    where dt='${do_date}' ) t1
		where create_time=min_order_create_time
		)c
on a.category_name=c.category_name and a.user_id=b.user_id;
"

dws_user_retention_subject_lidan_1d="
with subject_order as(
	select t1.id,
	       t1.course_id,
	       t1.course_name,
	       t2.subject_name,
	       t2.category_name,
	       t1.order_id,
	       t1.user_id,
	       t1.origin_amount,
	       t1.coupon_reduce,
	       t1.final_amount,
	       t1.create_time,
	       t1.update_time,
	       t1.dt
	from dwd_order_detail_lidan_inc t1
	     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
	WHERE t1.dt='${do_date}' and t2.dt='${do_date}'
                     )


insert overwrite table dws_user_retention_subject_lidan_1d partition(dt='${do_date}')



select a.subject_id,
       a.subject_name,
       a.user_id,
       nvl(b.create_time,a.create_time) create_time,
       nvl(b.final_amount,c.final_amount) final_amount,
       nvl(b.min_order_create_time,c.min_order_create_time) min_order_create_time,
       nvl(b.diff_day,datediff(nvl(b.min_order_create_time,c.min_order_create_time),nvl(b.create_time,a.create_time))+1),
       a.dt
from
	(
		select t2.subject_id,
		       t2.subject_name,
		       t1.user_id,
		       min(t1.create_time) create_time,
		       t1.dt
		from dwd_user_chapter_process_lidan_inc t1
		     left join dim_course_info_lidan_full t2 on t1.course_id=t2.id
		where t1.dt <= '${do_date}'
		  and t1.dt >= date_add('${do_date}', -6) and t2.dt='${do_date}'
		group by t2.subject_id,t2.subject_name,t1.user_id,t1.dt
	)a
left join
	(
		select subject_id,
		       subject_name,
		       user_id,
		       create_time,
		       final_amount,
		       min_order_create_time,
		       diff_day,
		       try_dt
	    from dws_user_retention_subject_lidan_1d
	    where dt=date_add('${do_date}',-1)
	)b
on a.subject_id=b.subject_id and a.user_id=b.user_id
left join(
		select subject_name,
		       user_id,
		       final_amount,
		       min_order_create_time
		from(
			    select subject_name,
			           user_id,
			           final_amount,
			           create_time,
			           min(create_time) over(partition by subject_name,user_id) min_order_create_time
			    from subject_order
			    where dt='${do_date}' ) t1
		where create_time=min_order_create_time
		)c
on a.subject_name=c.subject_name and a.user_id=b.user_id;
"
dws_user_retention_course_lidan_1d="
set hive.auto.convert.join=false;
insert overwrite table dws_user_retention_course_lidan_1d partition(dt='${do_date}')
select a.course_id,
       d.course_name,
       a.user_id,
       nvl(b.create_time,a.create_time) create_time,
       nvl(b.final_amount,c.final_amount) final_amount,
       nvl(b.min_order_create_time,c.min_order_create_time) min_order_create_time,
       nvl(b.diff_day,datediff(nvl(b.min_order_create_time,c.min_order_create_time),nvl(b.create_time,a.create_time))+1),
       a.dt
from
	(
		select course_id,
		       user_id,
		       min(create_time) create_time,
		       dt
		from dwd_user_chapter_process_lidan_inc
		where dt <= '${do_date}'
		  and dt >= date_add('${do_date}', -6)
		group by course_id,user_id,dt
	)a
left join
	(
		select course_id,
		       course_name,
		       user_id,
		       create_time,
		       final_amount,
		       min_order_create_time,
		       diff_day
	    from dws_user_retention_course_lidan_1d
	    where dt=date_add('${do_date}',-1)
	)b
on a.course_id=b.course_id and a.user_id=b.user_id
left join(
		select course_id,
		       user_id,
		       final_amount,
		       min_order_create_time
		from(
			    select course_id,
			           user_id,
			           final_amount,
			           create_time,
			           min(create_time) over(partition by course_id,user_id) min_order_create_time
			    from dwd_order_detail_lidan_inc
			    where dt='${do_date}' ) t1
		where create_time=min_order_create_time
		)c
on a.course_id=c.course_id and a.user_id=b.user_id
left join dim_course_info_lidan_full d ON a.course_id=d.id
where d.dt='${do_date}';
set hive.auto.convert.join=true;
"
dws_trade_user_province_order_1d="
insert overwrite table dws_trade_user_province_order_1d partition (dt = '${do_date}')
select user_id,
       province_id,
       province_name,
       area_code,
       iso_code,
       iso_3166_2,
       cast(order_count_1d as BIGINT),
       cast(order_num_1d as BIGINT),
       cast(order_original_amount_1d as DECIMAL(16,2)),
       cast(coupon_reduce_amount_1d as DECIMAL(16,2)),
       cast(order_total_amount_1d as DECIMAL(16,2))
from (
         select user_id,
                province_id,
                count(distinct order_id) order_count_1d,
                count(order_id)          order_num_1d,
                sum(split_origin_amount) order_original_amount_1d,
                sum(split_coupon_reduce) coupon_reduce_amount_1d,
                sum(split_total_amount) order_total_amount_1d,
                dt
         from dwd_trade_order_detail_inc
         where dt = '${do_date}'
         group by user_id, province_id, dt
     ) t1 left join (
    select id,
           province_name,
           area_code,
           iso_code,
           iso_3166_2,
           region_id
    from dim_province_full
    where dt = '${do_date}'
) t2 on t1.province_id = t2.id;
"
dws_traffic_session_page_view_1d="
insert overwrite table dws_traffic_session_page_view_1d partition(dt='${do_date}')
select session_id,
       mid_id,
       brand,
       model,
       operate_system,
       version_code,
       channel,
       count(page_id) page_count_1d,--一个会话访问页面数
       sum(during_time) during_time_1d --一个会话的访问时长
from dwd_traffic_page_view_inc
where dt='${do_date}'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"
dws_traffic_different_source_user_order_1d="
insert overwrite table dws_traffic_different_source_user_order_1d partition(dt='${do_date}')
select
       t3.user_id, --'用户id',
       payment_total_amount, -- '最近一日的总销售额',
       percent_conversion , --'最近一日的转化率',
        sourcetype_code --来源编码
from (select t1.user_id,                                              --'用户id',
             payment_total_amount,                                    -- '最近一日的总销售额',
             increase_order_user / none_order_user percent_conversion --'最近一日的转化率',
      from (select user_id,
                   sum(final_amount)               payment_total_amount,-- '最近一日的总销售额',
                   count(distinct user_id) over () increase_order_user  --从来没有下过单的人数中新增下单的用户数
            from dwd_order_detail_lidan_inc o
            where dt = '${do_date}'
            group by user_id) t1
               left join (
--从来没有下过单的人数
          select u.id                         user_id,
                 count(distinct u.id) over () none_order_user
          from dim_user_zip u
                   left join dwd_order_detail_lidan_inc o
                             on o.user_id = u.id
          group by u.id) t2
                         on t1.user_id = t2.user_id)t3
join dwd_traffic_page_view_inc p on p.user_id = t3.user_id;
"
dws_traffic_different_page_source_path_1d="
insert overwrite table dws_traffic_different_page_source_path_1d partition(dt='${do_date}')
select  session_id,
        mid_id,
        last_page_id,
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target,
        max(rn) over (partition by session_id) path_count
--   计算跳出率： if(last_page_id is null and next_page_id is null,1,0 ) over (partition by session_id)skip_tmp
from (select session_id,
             mid_id,
             last_page_id,
             page_id,
             lead(page_id, 1, null) over (partition by session_id order by view_time) next_page_id,
             row_number() over (partition by session_id order by view_time) rn
             -- 跳出率就是一个会话里面跳转页面数为 1 的
      from dwd_traffic_page_view_inc
      where dt = '${do_date}')t1;
"

case $1 in
    "dws_test_exam_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_exam_1d"
    ;;
    "dws_test_course_scorestage_1d" )
            /opt/module/hive/bin/hive -e "use ${APP};$dws_test_course_scorestage_1d"
    ;;
    "dws_test_question_1d" )
            /opt/module/hive/bin/hive -e "use ${APP};$dws_test_question_1d"
    ;;
    "dws_order_category_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_category_lidan_1d"
    ;;
    "dws_order_subject_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_subject_lidan_1d"
    ;;
    "dws_order_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_order_course_lidan_1d"
    ;;
    "dws_review_info_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_review_info_course_lidan_1d"
    ;;
    "dws_user_retention_category_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_category_lidan_1d"
    ;;
    "dws_user_retention_subject_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_subject_lidan_1d"
    ;;
    "dws_user_retention_course_lidan_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_user_retention_course_lidan_1d"
    ;;
    "dws_trade_user_province_order_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_trade_user_province_order_1d"
    ;;
    "dws_finishcourse_num_nd" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_finishcourse_num_nd"
    ;;

      "dws_traffic_session_page_view_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_traffic_session_page_view_1d"
    ;;
      "dws_traffic_different_source_user_order_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_traffic_different_source_user_order_1d"
    ;;
        "dws_traffic_different_page_source_path_1d" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_traffic_different_page_source_path_1d"
    ;;
    "all" )
        /opt/module/hive/bin/hive -e "
        use ${APP};
        $dws_test_exam_1d
        $dws_test_course_scorestage_1d
        $dws_test_question_1d
        $dws_order_category_lidan_1d
        $dws_order_subject_lidan_1d
        $dws_order_course_lidan_1d
        $dws_review_info_course_lidan_1d
        $dws_user_retention_category_lidan_1d
        $dws_user_retention_subject_lidan_1d
        $dws_user_retention_course_lidan_1d
        $dws_trade_user_province_order_1d
        $dws_finishcourse_num_nd
        $dws_traffic_session_page_view_1d
        $dws_traffic_different_source_user_order_1d
        $dws_traffic_different_page_source_path_1d
        "
    ;;
esac